from common import RatingEntity,SearchFilter
import DatabaseConnection

class RatingData(object):
    def __init__(self):
        pass

    def InsertRating(self,rating):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('InsRating',(rating.MemberID,rating.SongID,rating.Score))
        cursor.close()
        conn.commit()
        conn.close()

    def UpdateRating(self,rating):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('UpdRating',(rating.MemberID,rating.SongID,rating.Score))
        cursor.close()
        conn.commit()
        conn.close()

    def DeleteRating(self,mID,sID):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('DelRating',(mID,sID,))
        cursor.close()
        conn.commit()
        conn.close()

    #This method either return an object of type RatingEntity or None (if there is no record with this ID)
    def SelectRatingByID(self,memberID,songID):
        query = """ SELECT MemberID,SongID,Score,MemberName,SongName
                    FROM vwRating
                    WHERE MemberID='%s' AND SongID='%s'""" % (memberID,songID)

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return RatingEntity.RatingEntity(memberID=result[0][0],songID=result[0][1],score=result[0][2],memberName=result[0][3],songName=result[0][4])
        else:
            return None

    #This method returns a list of objects of type RatingEntity
    def SelectRatingByFilter(self,searchFilter):
        query = """ SELECT MemberID,SongID,Score,MemberName,SongName
                    FROM vwRating"""

        whereClause = searchFilter.GetWhereClause()
        if whereClause.strip()<>"":
            query += " WHERE %s" % whereClause

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(RatingEntity.RatingEntity(memberID=record[0],songID=record[1],score=record[2],memberName=record[3],songName=record[4]))
        return recordSet

    def SelectAllRatings(self):
        query = """ SELECT MemberID,SongID,Score,MemberName,SongName
                    FROM vwRating"""

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(RatingEntity.RatingEntity(memberID=record[0],songID=record[1],score=record[2],memberName=record[3],songName=record[4]))
        return recordSet
